{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "981888ff",
   "metadata": {},
   "source": [
    "# 导入必要库并且读取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "916ae69c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import json\n",
    "import os\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "87ae6bf6",
   "metadata": {},
   "outputs": [],
   "source": [
    "cities = ['东莞', '中山', '佛山', '广州', '惠州', '江门', '深圳', '清远', '湛江', '珠海']\n",
    "datazs = {}\n",
    "for city in cities:\n",
    "    file_path = f'./成交csv/成交_{city}.csv'\n",
    "    datazs[city] = pd.read_csv(file_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43db27f9",
   "metadata": {},
   "source": [
    "# 查询列表"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87afa9f2",
   "metadata": {},
   "source": [
    "共计29970条在售数据 \n",
    "\n",
    "\n",
    "'编号', '小区', '房型', '面积', '朝向', '装修', '楼层', '成交周期', '挂牌价格', '成交时间', '成交价格', '均价'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "471a4543",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "东莞成交数据量： (3030, 12)\n",
      "东莞成交列表： ['编号', '小区', '房型', '面积', '朝向', '装修', '楼层', '成交周期', '挂牌价格', '成交时间', '成交价格', '均价']\n",
      "中山成交数据量： (3000, 12)\n",
      "中山成交列表： ['编号', '小区', '房型', '面积', '朝向', '装修', '楼层', '成交周期', '挂牌价格', '成交时间', '成交价格', '均价']\n",
      "佛山成交数据量： (2939, 12)\n",
      "佛山成交列表： ['编号', '小区', '房型', '面积', '朝向', '装修', '楼层', '成交周期', '挂牌价格', '成交时间', '成交价格', '均价']\n",
      "广州成交数据量： (3000, 12)\n",
      "广州成交列表： ['编号', '小区', '房型', '面积', '朝向', '装修', '楼层', '成交周期', '挂牌价格', '成交时间', '成交价格', '均价']\n",
      "惠州成交数据量： (3000, 12)\n",
      "惠州成交列表： ['编号', '小区', '房型', '面积', '朝向', '装修', '楼层', '成交周期', '挂牌价格', '成交时间', '成交价格', '均价']\n",
      "江门成交数据量： (3000, 12)\n",
      "江门成交列表： ['编号', '小区', '房型', '面积', '朝向', '装修', '楼层', '成交周期', '挂牌价格', '成交时间', '成交价格', '均价']\n",
      "深圳成交数据量： (3000, 12)\n",
      "深圳成交列表： ['编号', '小区', '房型', '面积', '朝向', '装修', '楼层', '成交周期', '挂牌价格', '成交时间', '成交价格', '均价']\n",
      "清远成交数据量： (3000, 12)\n",
      "清远成交列表： ['编号', '小区', '房型', '面积', '朝向', '装修', '楼层', '成交周期', '挂牌价格', '成交时间', '成交价格', '均价']\n",
      "湛江成交数据量： (3000, 12)\n",
      "湛江成交列表： ['编号', '小区', '房型', '面积', '朝向', '装修', '楼层', '成交周期', '挂牌价格', '成交时间', '成交价格', '均价']\n",
      "珠海成交数据量： (3000, 12)\n",
      "珠海成交列表： ['编号', '小区', '房型', '面积', '朝向', '装修', '楼层', '成交周期', '挂牌价格', '成交时间', '成交价格', '均价']\n"
     ]
    }
   ],
   "source": [
    "for i in cities:\n",
    "    print(f'{i}成交数据量：',datazs[i].shape)\n",
    "    print(f'{i}成交列表：',datazs[i].columns.tolist())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac682723",
   "metadata": {},
   "source": [
    "# 缺失值处理\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b819c65",
   "metadata": {},
   "source": [
    "缺失值处理：东莞、中山、佛山、广州、惠州、江门、湛江、珠海在编号、面积、交易等属性上存在部分缺失值（由于页面部分交易数据暂无数据和报价导致的）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "1f10acd3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "东莞在售: 编号      194\n",
      "小区        0\n",
      "房型        0\n",
      "面积       51\n",
      "朝向        0\n",
      "装修        0\n",
      "楼层        0\n",
      "成交周期      0\n",
      "挂牌价格      0\n",
      "成交时间      0\n",
      "成交价格      0\n",
      "均价        0\n",
      "dtype: int64\n",
      "中山在售: 编号       0\n",
      "小区       0\n",
      "房型       0\n",
      "面积      63\n",
      "朝向       0\n",
      "装修       0\n",
      "楼层       0\n",
      "成交周期     0\n",
      "挂牌价格     0\n",
      "成交时间     0\n",
      "成交价格     0\n",
      "均价       0\n",
      "dtype: int64\n",
      "佛山在售: 编号      42\n",
      "小区       0\n",
      "房型       0\n",
      "面积      28\n",
      "朝向       0\n",
      "装修       0\n",
      "楼层       0\n",
      "成交周期     0\n",
      "挂牌价格     0\n",
      "成交时间     0\n",
      "成交价格     0\n",
      "均价       0\n",
      "dtype: int64\n",
      "广州在售: 编号      82\n",
      "小区       0\n",
      "房型       0\n",
      "面积      80\n",
      "朝向       0\n",
      "装修       0\n",
      "楼层       0\n",
      "成交周期     0\n",
      "挂牌价格     0\n",
      "成交时间     0\n",
      "成交价格     0\n",
      "均价       0\n",
      "dtype: int64\n",
      "惠州在售: 编号      171\n",
      "小区        0\n",
      "房型        0\n",
      "面积        1\n",
      "朝向        0\n",
      "装修        0\n",
      "楼层        0\n",
      "成交周期      0\n",
      "挂牌价格      0\n",
      "成交时间      0\n",
      "成交价格      0\n",
      "均价        0\n",
      "dtype: int64\n",
      "江门在售: 编号      0\n",
      "小区      0\n",
      "房型      0\n",
      "面积      7\n",
      "朝向      0\n",
      "装修      0\n",
      "楼层      0\n",
      "成交周期    0\n",
      "挂牌价格    0\n",
      "成交时间    0\n",
      "成交价格    0\n",
      "均价      0\n",
      "dtype: int64\n",
      "深圳在售: 编号      0\n",
      "小区      0\n",
      "房型      0\n",
      "面积      0\n",
      "朝向      0\n",
      "装修      0\n",
      "楼层      0\n",
      "成交周期    0\n",
      "挂牌价格    0\n",
      "成交时间    0\n",
      "成交价格    0\n",
      "均价      0\n",
      "dtype: int64\n",
      "清远在售: 编号      251\n",
      "小区        0\n",
      "房型        0\n",
      "面积        8\n",
      "朝向        0\n",
      "装修        0\n",
      "楼层        0\n",
      "成交周期      0\n",
      "挂牌价格      0\n",
      "成交时间      0\n",
      "成交价格      0\n",
      "均价        0\n",
      "dtype: int64\n",
      "湛江在售: 编号       0\n",
      "小区       0\n",
      "房型       0\n",
      "面积      17\n",
      "朝向       0\n",
      "装修       0\n",
      "楼层       0\n",
      "成交周期     0\n",
      "挂牌价格     0\n",
      "成交时间     0\n",
      "成交价格     0\n",
      "均价       0\n",
      "dtype: int64\n",
      "珠海在售: 编号       6\n",
      "小区       0\n",
      "房型       0\n",
      "面积      30\n",
      "朝向       0\n",
      "装修       0\n",
      "楼层       0\n",
      "成交周期     0\n",
      "挂牌价格     0\n",
      "成交时间     0\n",
      "成交价格     0\n",
      "均价       0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "for i in cities:\n",
    "    print(f'{i}在售:',datazs[i].isnull().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "35bf81b9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "东莞成交数据中存在缺失值的行：共214行\n",
      "\n",
      "中山成交数据中存在缺失值的行：共63行\n",
      "\n",
      "佛山成交数据中存在缺失值的行：共63行\n",
      "\n",
      "广州成交数据中存在缺失值的行：共150行\n",
      "\n",
      "惠州成交数据中存在缺失值的行：共172行\n",
      "\n",
      "江门成交数据中存在缺失值的行：共7行\n",
      "\n",
      "清远成交数据中存在缺失值的行：共252行\n",
      "\n",
      "湛江成交数据中存在缺失值的行：共17行\n",
      "\n",
      "珠海成交数据中存在缺失值的行：共36行\n"
     ]
    }
   ],
   "source": [
    "for city in cities:\n",
    "    df = datazs[city]\n",
    "    missing_rows = df[df.isnull().any(axis=1)]  # 筛选出存在缺失值的行\n",
    "    if not missing_rows.empty:\n",
    "        print(f\"\\n{city}成交数据中存在缺失值的行：共{len(missing_rows)}行\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "233489a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 循环删除各城市的车位数据\n",
    "for city in cities:\n",
    "    datazs[city] = datazs[city][datazs[city]['房型'] != '车位']\n",
    "    datazs[city] = datazs[city].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02effbf7",
   "metadata": {},
   "source": [
    "# 重复值处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "63ed77df",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "东莞市存在 99 条重复记录，重复比例: 3.32%\n",
      "中山市存在 86 条重复记录，重复比例: 2.93%\n",
      "佛山市存在 4 条重复记录，重复比例: 0.14%\n",
      "广州市存在 0 条重复记录，重复比例: 0.00%\n",
      "惠州市存在 0 条重复记录，重复比例: 0.00%\n",
      "江门市存在 0 条重复记录，重复比例: 0.00%\n",
      "深圳市存在 0 条重复记录，重复比例: 0.00%\n",
      "清远市存在 3 条重复记录，重复比例: 0.10%\n",
      "湛江市存在 3 条重复记录，重复比例: 0.10%\n",
      "珠海市存在 0 条重复记录，重复比例: 0.00%\n",
      "\n",
      "已将重复数据统计结果保存至: 重复统计结果\\成交重复数据统计.json\n",
      "东莞市已删除 99 条重复记录\n",
      "中山市已删除 86 条重复记录\n",
      "佛山市已删除 4 条重复记录\n",
      "清远市已删除 3 条重复记录\n",
      "湛江市已删除 3 条重复记录\n",
      "\n",
      "所有城市的重复数据已删除完毕\n"
     ]
    }
   ],
   "source": [
    "# 创建保存统计结果的字典\n",
    "duplicate_stats = {}\n",
    "\n",
    "for city in cities:\n",
    "    df = datazs[city]\n",
    "    \n",
    "    # 计算重复行数\n",
    "    duplicates = df[df.duplicated()]\n",
    "    duplicate_count = len(duplicates)\n",
    "    duplicate_ratio = duplicate_count / len(df) if len(df) > 0 else 0\n",
    "    \n",
    "    # 记录统计结果\n",
    "    duplicate_stats[city] = {\n",
    "        \"重复比例\": f\"{duplicate_ratio:.2%}\",\n",
    "        \"重复记录数量\": duplicate_count,\n",
    "    }\n",
    "    \n",
    "    print(f\"{city}市存在 {duplicate_count} 条重复记录，重复比例: {duplicate_ratio:.2%}\")\n",
    "\n",
    "output_dir = \"重复统计结果\"\n",
    "os.makedirs(output_dir, exist_ok=True)\n",
    "json_path = os.path.join(output_dir, \"成交重复数据统计.json\")\n",
    "\n",
    "with open(json_path, 'w', encoding='utf-8') as f:\n",
    "    json.dump(duplicate_stats, f, ensure_ascii=False, indent=2)\n",
    "\n",
    "print(f\"\\n已将重复数据统计结果保存至: {json_path}\")\n",
    "\n",
    "for city in cities:\n",
    "    original_len = len(datazs[city])\n",
    "    datazs[city] = datazs[city].drop_duplicates()\n",
    "    deleted_count = original_len - len(datazs[city])\n",
    "    \n",
    "    if deleted_count > 0:\n",
    "        print(f\"{city}市已删除 {deleted_count} 条重复记录\")\n",
    "    \n",
    "    # 重置索引\n",
    "    datazs[city] = datazs[city].reset_index(drop=True)\n",
    "\n",
    "print(\"\\n所有城市的重复数据已删除完毕\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "fdd0b45a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>小区</th>\n",
       "      <th>房型</th>\n",
       "      <th>面积</th>\n",
       "      <th>朝向</th>\n",
       "      <th>装修</th>\n",
       "      <th>楼层</th>\n",
       "      <th>成交周期</th>\n",
       "      <th>挂牌价格</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>成交价格</th>\n",
       "      <th>均价</th>\n",
       "      <th>城市</th>\n",
       "      <th>新编号</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>丰泰东海城堡</td>\n",
       "      <td>3室2厅</td>\n",
       "      <td>97.92平米</td>\n",
       "      <td>南</td>\n",
       "      <td>其他</td>\n",
       "      <td>低楼层(共15层)</td>\n",
       "      <td>暂无数据</td>\n",
       "      <td>暂无报价</td>\n",
       "      <td>2025.04.11</td>\n",
       "      <td>暂无报价</td>\n",
       "      <td>暂无报价</td>\n",
       "      <td>东莞</td>\n",
       "      <td>dg0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>万科花园</td>\n",
       "      <td>3室1厅</td>\n",
       "      <td>75.61平米</td>\n",
       "      <td>东北</td>\n",
       "      <td>其他</td>\n",
       "      <td>高楼层(共34层)</td>\n",
       "      <td>暂无数据</td>\n",
       "      <td>暂无报价</td>\n",
       "      <td>2025.04.11</td>\n",
       "      <td>暂无报价</td>\n",
       "      <td>暂无报价</td>\n",
       "      <td>东莞</td>\n",
       "      <td>dg1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>理想沁园</td>\n",
       "      <td>2室2厅</td>\n",
       "      <td>43.21平米</td>\n",
       "      <td>东南 东</td>\n",
       "      <td>精装</td>\n",
       "      <td>低楼层(共7层)</td>\n",
       "      <td>暂无数据</td>\n",
       "      <td>暂无报价</td>\n",
       "      <td>2025.04.11</td>\n",
       "      <td>暂无报价</td>\n",
       "      <td>暂无报价</td>\n",
       "      <td>东莞</td>\n",
       "      <td>dg2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>万科金色里程</td>\n",
       "      <td>3室2厅</td>\n",
       "      <td>97.76平米</td>\n",
       "      <td>南</td>\n",
       "      <td>精装</td>\n",
       "      <td>中楼层(共33层)</td>\n",
       "      <td>暂无数据</td>\n",
       "      <td>暂无报价</td>\n",
       "      <td>2025.04.11</td>\n",
       "      <td>暂无报价</td>\n",
       "      <td>暂无报价</td>\n",
       "      <td>东莞</td>\n",
       "      <td>dg3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>凯晟景园</td>\n",
       "      <td>3室2厅</td>\n",
       "      <td>106.76平米</td>\n",
       "      <td>南</td>\n",
       "      <td>毛坯</td>\n",
       "      <td>低楼层(共26层)</td>\n",
       "      <td>暂无数据</td>\n",
       "      <td>暂无报价</td>\n",
       "      <td>2025.04.11</td>\n",
       "      <td>暂无报价</td>\n",
       "      <td>暂无报价</td>\n",
       "      <td>东莞</td>\n",
       "      <td>dg4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       小区    房型        面积    朝向  装修         楼层  成交周期  挂牌价格        成交时间  成交价格  \\\n",
       "0  丰泰东海城堡  3室2厅   97.92平米     南  其他  低楼层(共15层)  暂无数据  暂无报价  2025.04.11  暂无报价   \n",
       "1    万科花园  3室1厅   75.61平米    东北  其他  高楼层(共34层)  暂无数据  暂无报价  2025.04.11  暂无报价   \n",
       "2    理想沁园  2室2厅   43.21平米  东南 东  精装   低楼层(共7层)  暂无数据  暂无报价  2025.04.11  暂无报价   \n",
       "3  万科金色里程  3室2厅   97.76平米     南  精装  中楼层(共33层)  暂无数据  暂无报价  2025.04.11  暂无报价   \n",
       "4    凯晟景园  3室2厅  106.76平米     南  毛坯  低楼层(共26层)  暂无数据  暂无报价  2025.04.11  暂无报价   \n",
       "\n",
       "     均价  城市  新编号  \n",
       "0  暂无报价  东莞  dg0  \n",
       "1  暂无报价  东莞  dg1  \n",
       "2  暂无报价  东莞  dg2  \n",
       "3  暂无报价  东莞  dg3  \n",
       "4  暂无报价  东莞  dg4  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "city_pinyin_map = {\n",
    "    \"东莞\": \"dg\",\n",
    "    \"中山\": \"zs\",\n",
    "    \"佛山\": \"fs\",\n",
    "    \"广州\": \"gz\",\n",
    "    \"惠州\": \"hz\",\n",
    "    \"江门\": \"jm\",\n",
    "    \"深圳\": \"sz\",\n",
    "    \"清远\": \"qy\",\n",
    "    \"湛江\": \"zj\",\n",
    "    \"珠海\": \"zh\"\n",
    "}\n",
    "for city in cities:\n",
    "    df = datazs[city]\n",
    "    df['城市'] = city\n",
    "    city_pinyin = city_pinyin_map[city]\n",
    "    df['新编号'] = [f\"{city_pinyin}{i}\" for i in df.index] # 添加新编号列\n",
    "    df = df.drop('编号',axis=1)\n",
    "    datazs[city] = df # 更新原始数据\n",
    "datazs['东莞'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "375ff0a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "missing_cols = ['成交周期', '挂牌价格', '成交价格', '均价']\n",
    "for city,df in datazs.items():\n",
    "    df[missing_cols] = df[missing_cols].replace('暂无数据', pd.NA)\n",
    "    df[missing_cols] = df[missing_cols].replace('暂无报价', pd.NA)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "fba5bdb3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 总价分类阈值（万元）\n",
    "PRICE_LEVELS = {\n",
    "    '50万以内': (0, 50),\n",
    "    '50-100万': (50, 100),\n",
    "    '100-200万': (100, 200),\n",
    "    '200万以上': (200, np.inf)\n",
    "}\n",
    "\n",
    "# 均价分类阈值（万元/平米）\n",
    "UNIT_PRICE_LEVELS = {\n",
    "    '1万以内': (0, 10000),\n",
    "    '1-3万': (10000, 30000),\n",
    "    '3-5万': (30000, 50000),\n",
    "    '5万以上': (50000, np.inf)\n",
    "}\n",
    "\n",
    "# 面积（平米）\n",
    "UNIT_SIZE = {\n",
    "    '小户型':{0,90},\n",
    "    '中户型':(90,144),\n",
    "    '大户型':(144,200),\n",
    "    '超大户型':(200,np.inf)\n",
    "}\n",
    "# 成交时间分类阈值（天数）\n",
    "TIME_LEVELS = {\n",
    "    '近一个月成交': (0, 30),\n",
    "    '近三个月成交': (30, 90),\n",
    "    '近六个月成交': (90, 180),\n",
    "    '半年以上成交': (180, np.inf)\n",
    "}\n",
    "#金额差价映射\n",
    "DIFFERENCE_LEVELS = {\n",
    "            '大幅降价': (float('-inf'), -100),\n",
    "            '明显降价': (-100, -30),\n",
    "            '小幅降价': (-30, -5),\n",
    "            '基本持平': (-5, 5),\n",
    "            '小幅溢价': (5, 30),\n",
    "            '明显溢价': (30, 100),\n",
    "            '大幅溢价': (100, float('inf'))\n",
    "        }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "38648560",
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_data(df):\n",
    "    # 一、处理面积列，提取数字部分\n",
    "    df['面积'] = df['面积'].astype(str).apply(lambda x: re.search(r'(\\d+\\.?\\d*)', x).group(1) if re.search(r'(\\d+\\.?\\d*)', x) else np.nan)\n",
    "    df['面积'] = pd.to_numeric(df['面积'], errors='coerce')\n",
    "    \n",
    "    # 二、处理成交价格列，提取数字部分\n",
    "    df['成交价格'] = df['成交价格'].astype(str).apply(lambda x: re.search(r'(\\d+\\.?\\d*)', x).group(1) if re.search(r'(\\d+\\.?\\d*)', x) else np.nan)\n",
    "    df['成交价格'] = pd.to_numeric(df['成交价格'], errors='coerce')\n",
    "    \n",
    "    # 三、处理均价列，提取数字部分\n",
    "    df['均价'] = df['均价'].astype(str).apply(lambda x: re.search(r'(\\d+\\.?\\d*)', x).group(1) if re.search(r'(\\d+\\.?\\d*)', x) else np.nan)\n",
    "    df['均价'] = pd.to_numeric(df['均价'], errors='coerce')\n",
    "    \n",
    "    \n",
    "    # 四、处理户型列，拆分为室和厅\n",
    "    def parse_room_hall(house_type):\n",
    "        house_type = str(house_type)\n",
    "        match = re.search(r'(\\d+)室(\\d+)厅', house_type)\n",
    "        if match:\n",
    "            return pd.Series([int(match.group(1)), int(match.group(2))])\n",
    "        else:\n",
    "            return pd.Series([np.nan, np.nan])\n",
    "    \n",
    "    df[['室', '厅']] = df['房型'].apply(lambda x: pd.Series(parse_room_hall(x))).fillna(0).astype(int)\n",
    "    \n",
    "    \n",
    "    #五、楼层等级\n",
    "    def classify_floor(floor_str):\n",
    "        # 先检查是否包含低/中/高关键词\n",
    "        for level in ['低', '中', '高']:\n",
    "            if level in floor_str:\n",
    "                return f\"{level}楼层\"\n",
    "        # 处理纯数字楼层（如\"30层\"）\n",
    "        match = re.match(r'^(\\d+)层$', floor_str)\n",
    "        FLOOR_RULES = {\n",
    "            '低楼层': (1, 10),   # 1-10层\n",
    "            '中楼层': (11, 20),  # 11-20层\n",
    "            '高楼层': (21, np.inf)  # 21层以上\n",
    "        }\n",
    "        if match:\n",
    "            floor_num = int(match.group(1))\n",
    "            for category, (low, high) in FLOOR_RULES.items():\n",
    "                if low <= floor_num <= high:  # 包含边界值\n",
    "                    return category\n",
    "        if '地下室' in floor_str:\n",
    "            return '地下室'\n",
    "        return '未分类'\n",
    "    \n",
    "    df['楼层等级'] = df['楼层'].apply(classify_floor)\n",
    "    \n",
    "    \n",
    "    \n",
    "    #六、成交价格区间\n",
    "    def class_price(p):\n",
    "        if pd.notna(p):\n",
    "            for level, (min_num, max_num) in PRICE_LEVELS.items():\n",
    "                if min_num <= p <= max_num:\n",
    "                    return level\n",
    "        return '未分类（暂无数据）'\n",
    "    df['成交价格区间'] = df['成交价格'].apply(class_price)\n",
    "    \n",
    "    #七、均价区间\n",
    "    def class_uprice(p):\n",
    "        if pd.notna(p):\n",
    "            for level, (min_num, max_num) in UNIT_PRICE_LEVELS.items():\n",
    "                if min_num <= p <= max_num:\n",
    "                    return level\n",
    "        return '未分类（暂无数据）'\n",
    "    df['均价区间'] = df['均价'].apply(class_uprice)\n",
    "    \n",
    "    #八、朝向划分\n",
    "    def map_orientation_type(orientation):\n",
    "        directions = orientation.split()  # 拆分方向列表\n",
    "        dir_set = set(directions)  # 去重\n",
    "\n",
    "        # 单一朝向（8个基础方向）\n",
    "        base_directions = {'东', '南', '西', '北', '东北', '东南', '西北', '西南'}\n",
    "        if len(dir_set) == 1 and dir_set.issubset(base_directions):\n",
    "            return '单一朝向'\n",
    "\n",
    "        # 南北通透（必须同时包含南和北）\n",
    "        if '南' in dir_set and '北' in dir_set:\n",
    "            return '南北通透'\n",
    "\n",
    "        # 东西通透（必须同时包含东和西）\n",
    "        if '东' in dir_set and '西' in dir_set:\n",
    "            return '东西通透'\n",
    "\n",
    "        # 双向组合（2个方向，非南北/东西通透）\n",
    "        if len(dir_set) == 2:\n",
    "            return '双向组合'\n",
    "\n",
    "        # 复杂组合（3个及以上方向）\n",
    "        if len(dir_set) >= 3:\n",
    "            return '复杂组合'\n",
    "\n",
    "        # 异常值（如空值或无效方向）\n",
    "        return '未知'\n",
    "    \n",
    "    df['朝向分类'] = df['朝向'].apply(map_orientation_type)\n",
    "    \n",
    "    #九、面积户型\n",
    "    def class_Unitsize(u):\n",
    "        if pd.notna(u):\n",
    "            for level, (min_num, max_num) in UNIT_SIZE.items():\n",
    "                if min_num <= u <= max_num:\n",
    "                    return level\n",
    "        return '未分类'\n",
    "    df['面积户型'] = df['面积'].apply(class_Unitsize)\n",
    "    \n",
    "    \n",
    "    #十、成交周期区间\n",
    "    df['成交周期'] = pd.to_numeric(df['成交周期'], errors='coerce')\n",
    "    def class_time(t):\n",
    "        if pd.notna(t):\n",
    "            for level, (min_num, max_num) in TIME_LEVELS.items():\n",
    "                if min_num <= t <= max_num:\n",
    "                    return level\n",
    "        return '未分类（暂无数据）'\n",
    "    \n",
    "    df['成交周期区间'] = df['成交周期'].apply(class_time)\n",
    "    \n",
    "    #十一、实际成交与预期差价（挂牌价格-成交价）\n",
    "    def calculate_price_difference(df,挂牌价_col='挂牌价格',成交价_col='成交价格'):\n",
    "        # 处理非数值值，转换为NaN\n",
    "        df[挂牌价_col] = pd.to_numeric(df[挂牌价_col], errors='coerce')\n",
    "        df[成交价_col] = pd.to_numeric(df[成交价_col], errors='coerce')\n",
    "        # 创建有效数据掩码\n",
    "        valid_mask = df[挂牌价_col].notna() & df[成交价_col].notna()\n",
    "\n",
    "        # 计算差价（仅对有效数据）\n",
    "        df['差价（万）'] = np.nan\n",
    "        df.loc[valid_mask, '差价（万）'] = df.loc[valid_mask,成交价_col] - df.loc[valid_mask,挂牌价_col]\n",
    "        df['差价分类（金额）'] = df['差价（万）'].apply(\n",
    "            lambda x: next((level for level, (min_val, max_val) in DIFFERENCE_LEVELS.items() \n",
    "                          if min_val <= x < max_val), '未分类') \n",
    "            if pd.notna(x) else '未分类（暂无数据）'\n",
    "        )\n",
    "\n",
    "        # 添加数据状态标记\n",
    "        df['价格数据状态'] = np.where(valid_mask, '有效数据', '暂无数据')\n",
    "\n",
    "        return df\n",
    "    calculate_price_difference(df)\n",
    "    \n",
    "    return df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "53533a9e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "已处理 东莞 的数据\n",
      "已处理 中山 的数据\n",
      "已处理 佛山 的数据\n",
      "已处理 广州 的数据\n",
      "已处理 惠州 的数据\n",
      "已处理 江门 的数据\n",
      "已处理 深圳 的数据\n",
      "已处理 清远 的数据\n",
      "已处理 湛江 的数据\n",
      "已处理 珠海 的数据\n"
     ]
    }
   ],
   "source": [
    "for city, df in datazs.items():\n",
    "    if not df.empty:\n",
    "        datazs[city] = process_data(df)\n",
    "        print(f\"已处理 {city} 的数据\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "91c013cb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "===========================东莞:室================================\n",
      "室\n",
      "3     1573\n",
      "2      530\n",
      "4      475\n",
      "1      208\n",
      "5       69\n",
      "6       14\n",
      "0        5\n",
      "8        3\n",
      "7        2\n",
      "18       1\n",
      "Name: count, dtype: int64\n",
      "===========================东莞:厅================================\n",
      "厅\n",
      "2    2133\n",
      "1     656\n",
      "0      64\n",
      "3      27\n",
      "Name: count, dtype: int64\n",
      "===========================东莞:朝向分类================================\n",
      "朝向分类\n",
      "单一朝向    2497\n",
      "南北通透     278\n",
      "双向组合     100\n",
      "复杂组合       3\n",
      "东西通透       2\n",
      "Name: count, dtype: int64\n",
      "===========================东莞:装修================================\n",
      "装修\n",
      "精装    1407\n",
      "其他     772\n",
      "简装     409\n",
      "毛坯     292\n",
      "Name: count, dtype: int64\n",
      "===========================东莞:楼层等级================================\n",
      "楼层等级\n",
      "中楼层    1117\n",
      "高楼层     914\n",
      "低楼层     847\n",
      "地下室       2\n",
      "Name: count, dtype: int64\n",
      "===========================东莞:成交价格区间================================\n",
      "成交价格区间\n",
      "未分类（暂无数据）    2880\n",
      "Name: count, dtype: int64\n",
      "===========================东莞:均价区间================================\n",
      "均价区间\n",
      "未分类（暂无数据）    2880\n",
      "Name: count, dtype: int64\n",
      "===========================东莞:面积户型================================\n",
      "面积户型\n",
      "中户型     1591\n",
      "小户型     1038\n",
      "大户型      183\n",
      "超大户型      68\n",
      "Name: count, dtype: int64\n",
      "===========================东莞:成交周期区间================================\n",
      "成交周期区间\n",
      "未分类（暂无数据）    2880\n",
      "Name: count, dtype: int64\n",
      "===========================中山:室================================\n",
      "室\n",
      "3    1705\n",
      "4     482\n",
      "2     438\n",
      "1     145\n",
      "5      55\n",
      "6      14\n",
      "7       7\n",
      "8       3\n",
      "0       2\n",
      "Name: count, dtype: int64\n",
      "===========================中山:厅================================\n",
      "厅\n",
      "2    2286\n",
      "1     510\n",
      "0      28\n",
      "3      23\n",
      "4       4\n",
      "Name: count, dtype: int64\n",
      "===========================中山:朝向分类================================\n",
      "朝向分类\n",
      "单一朝向    2232\n",
      "南北通透     515\n",
      "双向组合      97\n",
      "东西通透       5\n",
      "复杂组合       2\n",
      "Name: count, dtype: int64\n",
      "===========================中山:装修================================\n",
      "装修\n",
      "精装    1045\n",
      "其他     810\n",
      "毛坯     707\n",
      "简装     289\n",
      "Name: count, dtype: int64\n",
      "===========================中山:楼层等级================================\n",
      "楼层等级\n",
      "中楼层    1151\n",
      "高楼层     908\n",
      "低楼层     790\n",
      "地下室       2\n",
      "Name: count, dtype: int64\n",
      "===========================中山:成交价格区间================================\n",
      "成交价格区间\n",
      "50-100万     1608\n",
      "100-200万     584\n",
      "50万以内        565\n",
      "200万以上        94\n",
      "Name: count, dtype: int64\n",
      "===========================中山:均价区间================================\n",
      "均价区间\n",
      "1万以内    2414\n",
      "1-3万     437\n",
      "Name: count, dtype: int64\n",
      "===========================中山:面积户型================================\n",
      "面积户型\n",
      "中户型     1763\n",
      "小户型      867\n",
      "大户型      135\n",
      "超大户型      86\n",
      "Name: count, dtype: int64\n",
      "===========================中山:成交周期区间================================\n",
      "成交周期区间\n",
      "半年以上成交    1250\n",
      "近三个月成交     597\n",
      "近六个月成交     585\n",
      "近一个月成交     419\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:室================================\n",
      "室\n",
      "3     1653\n",
      "4      575\n",
      "2      372\n",
      "1      249\n",
      "5       41\n",
      "6        8\n",
      "7        5\n",
      "8        2\n",
      "17       1\n",
      "10       1\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:厅================================\n",
      "厅\n",
      "2    2040\n",
      "1     810\n",
      "0      46\n",
      "3       9\n",
      "4       2\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:朝向分类================================\n",
      "朝向分类\n",
      "单一朝向    2333\n",
      "南北通透     433\n",
      "双向组合     130\n",
      "东西通透       7\n",
      "复杂组合       4\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:装修================================\n",
      "装修\n",
      "精装    2002\n",
      "简装     494\n",
      "毛坯     362\n",
      "其他      49\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:楼层等级================================\n",
      "楼层等级\n",
      "中楼层    1155\n",
      "高楼层     944\n",
      "低楼层     806\n",
      "地下室       2\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:成交价格区间================================\n",
      "成交价格区间\n",
      "未分类（暂无数据）    2859\n",
      "200万以上         24\n",
      "100-200万       16\n",
      "50-100万         8\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:均价区间================================\n",
      "均价区间\n",
      "未分类（暂无数据）    2859\n",
      "1-3万           41\n",
      "3-5万            7\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:面积户型================================\n",
      "面积户型\n",
      "中户型     1583\n",
      "小户型     1135\n",
      "大户型      133\n",
      "超大户型      56\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:成交周期区间================================\n",
      "成交周期区间\n",
      "未分类（暂无数据）    2907\n",
      "Name: count, dtype: int64\n",
      "===========================广州:室================================\n",
      "室\n",
      "3    1330\n",
      "2     887\n",
      "4     355\n",
      "1     261\n",
      "5      62\n",
      "6      18\n",
      "0       2\n",
      "7       2\n",
      "9       2\n",
      "8       1\n",
      "Name: count, dtype: int64\n",
      "===========================广州:厅================================\n",
      "厅\n",
      "2    1490\n",
      "1    1354\n",
      "0      58\n",
      "3      14\n",
      "4       2\n",
      "9       2\n",
      "Name: count, dtype: int64\n",
      "===========================广州:朝向分类================================\n",
      "朝向分类\n",
      "单一朝向    2389\n",
      "南北通透     289\n",
      "双向组合     184\n",
      "东西通透      41\n",
      "复杂组合      17\n",
      "Name: count, dtype: int64\n",
      "===========================广州:装修================================\n",
      "装修\n",
      "精装    1336\n",
      "其他     765\n",
      "简装     703\n",
      "毛坯     116\n",
      "Name: count, dtype: int64\n",
      "===========================广州:楼层等级================================\n",
      "楼层等级\n",
      "中楼层    1159\n",
      "高楼层     920\n",
      "低楼层     841\n",
      "Name: count, dtype: int64\n",
      "===========================广州:成交价格区间================================\n",
      "成交价格区间\n",
      "200万以上       1180\n",
      "100-200万     1054\n",
      "50-100万       524\n",
      "50万以内         141\n",
      "未分类（暂无数据）      21\n",
      "Name: count, dtype: int64\n",
      "===========================广州:均价区间================================\n",
      "均价区间\n",
      "1-3万         1712\n",
      "3-5万          626\n",
      "1万以内          410\n",
      "5万以上          151\n",
      "未分类（暂无数据）      21\n",
      "Name: count, dtype: int64\n",
      "===========================广州:面积户型================================\n",
      "面积户型\n",
      "小户型     1531\n",
      "中户型     1173\n",
      "大户型      145\n",
      "超大户型      71\n",
      "Name: count, dtype: int64\n",
      "===========================广州:成交周期区间================================\n",
      "成交周期区间\n",
      "半年以上成交       1186\n",
      "近一个月成交        623\n",
      "近六个月成交        557\n",
      "近三个月成交        533\n",
      "未分类（暂无数据）      21\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:室================================\n",
      "室\n",
      "3    1363\n",
      "4     760\n",
      "2     465\n",
      "1     237\n",
      "5     145\n",
      "6      22\n",
      "7       6\n",
      "9       1\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:厅================================\n",
      "厅\n",
      "2    2312\n",
      "1     620\n",
      "0      45\n",
      "3      19\n",
      "4       2\n",
      "5       1\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:朝向分类================================\n",
      "朝向分类\n",
      "单一朝向    2253\n",
      "南北通透     625\n",
      "双向组合     108\n",
      "复杂组合       8\n",
      "东西通透       5\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:装修================================\n",
      "装修\n",
      "精装    1149\n",
      "毛坯     972\n",
      "其他     516\n",
      "简装     362\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:楼层等级================================\n",
      "楼层等级\n",
      "中楼层    1172\n",
      "低楼层     913\n",
      "高楼层     913\n",
      "地下室       1\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:成交价格区间================================\n",
      "成交价格区间\n",
      "50-100万     1530\n",
      "50万以内        681\n",
      "100-200万     677\n",
      "200万以上       111\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:均价区间================================\n",
      "均价区间\n",
      "1万以内    2394\n",
      "1-3万     602\n",
      "3-5万       2\n",
      "5万以上       1\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:面积户型================================\n",
      "面积户型\n",
      "中户型     1590\n",
      "小户型     1196\n",
      "大户型      128\n",
      "超大户型      85\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:成交周期区间================================\n",
      "成交周期区间\n",
      "半年以上成交    1088\n",
      "近三个月成交     651\n",
      "近一个月成交     648\n",
      "近六个月成交     612\n",
      "Name: count, dtype: int64\n",
      "===========================江门:室================================\n",
      "室\n",
      "3    1649\n",
      "2     579\n",
      "4     513\n",
      "1     163\n",
      "5      65\n",
      "6      15\n",
      "0       7\n",
      "7       2\n",
      "Name: count, dtype: int64\n",
      "===========================江门:厅================================\n",
      "厅\n",
      "2    2367\n",
      "1     567\n",
      "0      43\n",
      "3      14\n",
      "6       1\n",
      "4       1\n",
      "Name: count, dtype: int64\n",
      "===========================江门:朝向分类================================\n",
      "朝向分类\n",
      "单一朝向    2468\n",
      "南北通透     418\n",
      "双向组合      98\n",
      "东西通透       7\n",
      "复杂组合       2\n",
      "Name: count, dtype: int64\n",
      "===========================江门:装修================================\n",
      "装修\n",
      "精装    1421\n",
      "其他     604\n",
      "毛坯     527\n",
      "简装     441\n",
      "Name: count, dtype: int64\n",
      "===========================江门:楼层等级================================\n",
      "楼层等级\n",
      "中楼层    1204\n",
      "高楼层    1060\n",
      "低楼层     729\n",
      "Name: count, dtype: int64\n",
      "===========================江门:成交价格区间================================\n",
      "成交价格区间\n",
      "50-100万     1362\n",
      "50万以内       1056\n",
      "100-200万     507\n",
      "200万以上        68\n",
      "Name: count, dtype: int64\n",
      "===========================江门:均价区间================================\n",
      "均价区间\n",
      "1万以内    2656\n",
      "1-3万     337\n",
      "Name: count, dtype: int64\n",
      "===========================江门:面积户型================================\n",
      "面积户型\n",
      "中户型     1609\n",
      "小户型     1136\n",
      "大户型      171\n",
      "超大户型      74\n",
      "未分类        3\n",
      "Name: count, dtype: int64\n",
      "===========================江门:成交周期区间================================\n",
      "成交周期区间\n",
      "半年以上成交    1123\n",
      "近三个月成交     691\n",
      "近一个月成交     628\n",
      "近六个月成交     551\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:室================================\n",
      "室\n",
      "3    1154\n",
      "2     762\n",
      "1     485\n",
      "4     452\n",
      "5      99\n",
      "6      26\n",
      "7       9\n",
      "8       9\n",
      "0       4\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:厅================================\n",
      "厅\n",
      "2    1620\n",
      "1    1199\n",
      "0     167\n",
      "3      13\n",
      "4       1\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:朝向分类================================\n",
      "朝向分类\n",
      "单一朝向    2670\n",
      "南北通透     191\n",
      "双向组合     118\n",
      "复杂组合      11\n",
      "东西通透      10\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:装修================================\n",
      "装修\n",
      "精装    1309\n",
      "其他    1004\n",
      "简装     583\n",
      "毛坯     104\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:楼层等级================================\n",
      "楼层等级\n",
      "中楼层    1228\n",
      "高楼层     928\n",
      "低楼层     844\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:成交价格区间================================\n",
      "成交价格区间\n",
      "200万以上      2429\n",
      "100-200万     454\n",
      "50-100万      108\n",
      "50万以内          9\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:均价区间================================\n",
      "均价区间\n",
      "5万以上    1256\n",
      "3-5万    1244\n",
      "1-3万     500\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:面积户型================================\n",
      "面积户型\n",
      "小户型     1989\n",
      "中户型      786\n",
      "大户型      181\n",
      "超大户型      42\n",
      "未分类        2\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:成交周期区间================================\n",
      "成交周期区间\n",
      "半年以上成交    1105\n",
      "近一个月成交     734\n",
      "近六个月成交     685\n",
      "近三个月成交     476\n",
      "Name: count, dtype: int64\n",
      "===========================清远:室================================\n",
      "室\n",
      "3     1595\n",
      "2      559\n",
      "4      552\n",
      "1      190\n",
      "5       69\n",
      "6        9\n",
      "7        8\n",
      "9        4\n",
      "8        1\n",
      "20       1\n",
      "0        1\n",
      "Name: count, dtype: int64\n",
      "===========================清远:厅================================\n",
      "厅\n",
      "2    2185\n",
      "1     743\n",
      "0      29\n",
      "3      27\n",
      "4       3\n",
      "6       2\n",
      "Name: count, dtype: int64\n",
      "===========================清远:朝向分类================================\n",
      "朝向分类\n",
      "单一朝向    2435\n",
      "南北通透     368\n",
      "双向组合     150\n",
      "复杂组合      23\n",
      "东西通透      13\n",
      "Name: count, dtype: int64\n",
      "===========================清远:装修================================\n",
      "装修\n",
      "精装    1405\n",
      "其他     824\n",
      "毛坯     476\n",
      "简装     284\n",
      "Name: count, dtype: int64\n",
      "===========================清远:楼层等级================================\n",
      "楼层等级\n",
      "中楼层    1127\n",
      "高楼层     974\n",
      "低楼层     888\n",
      "Name: count, dtype: int64\n",
      "===========================清远:成交价格区间================================\n",
      "成交价格区间\n",
      "未分类（暂无数据）    2989\n",
      "Name: count, dtype: int64\n",
      "===========================清远:均价区间================================\n",
      "均价区间\n",
      "未分类（暂无数据）    2989\n",
      "Name: count, dtype: int64\n",
      "===========================清远:面积户型================================\n",
      "面积户型\n",
      "中户型     1567\n",
      "小户型     1099\n",
      "大户型      215\n",
      "超大户型     108\n",
      "Name: count, dtype: int64\n",
      "===========================清远:成交周期区间================================\n",
      "成交周期区间\n",
      "未分类（暂无数据）    2989\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:室================================\n",
      "室\n",
      "3    1352\n",
      "4     799\n",
      "2     464\n",
      "1     198\n",
      "5     150\n",
      "6      13\n",
      "7       2\n",
      "8       1\n",
      "9       1\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:厅================================\n",
      "厅\n",
      "2    2426\n",
      "1     483\n",
      "0      53\n",
      "3      14\n",
      "4       2\n",
      "5       1\n",
      "7       1\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:朝向分类================================\n",
      "朝向分类\n",
      "单一朝向    2597\n",
      "南北通透     226\n",
      "双向组合     145\n",
      "东西通透       7\n",
      "复杂组合       5\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:装修================================\n",
      "装修\n",
      "精装    1696\n",
      "毛坯     627\n",
      "简装     520\n",
      "其他     137\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:楼层等级================================\n",
      "楼层等级\n",
      "中楼层    1170\n",
      "高楼层    1032\n",
      "低楼层     778\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:成交价格区间================================\n",
      "成交价格区间\n",
      "50-100万     1284\n",
      "100-200万     822\n",
      "50万以内        809\n",
      "200万以上        65\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:均价区间================================\n",
      "均价区间\n",
      "1万以内    2330\n",
      "1-3万     650\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:面积户型================================\n",
      "面积户型\n",
      "中户型     1777\n",
      "小户型      896\n",
      "大户型      262\n",
      "超大户型      45\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:成交周期区间================================\n",
      "成交周期区间\n",
      "半年以上成交    1105\n",
      "近一个月成交     695\n",
      "近三个月成交     627\n",
      "近六个月成交     553\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:室================================\n",
      "室\n",
      "3    1404\n",
      "2     567\n",
      "4     484\n",
      "1     367\n",
      "5      85\n",
      "0      39\n",
      "6      15\n",
      "7       7\n",
      "8       2\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:厅================================\n",
      "厅\n",
      "2    2134\n",
      "1     636\n",
      "0     170\n",
      "3      27\n",
      "4       3\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:朝向分类================================\n",
      "朝向分类\n",
      "单一朝向    2378\n",
      "南北通透     434\n",
      "双向组合     139\n",
      "复杂组合      14\n",
      "东西通透       5\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:装修================================\n",
      "装修\n",
      "精装    1543\n",
      "其他     736\n",
      "简装     436\n",
      "毛坯     255\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:楼层等级================================\n",
      "楼层等级\n",
      "中楼层    1227\n",
      "高楼层     903\n",
      "低楼层     839\n",
      "地下室       1\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:成交价格区间================================\n",
      "成交价格区间\n",
      "100-200万    1108\n",
      "200万以上       866\n",
      "50-100万      789\n",
      "50万以内        207\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:均价区间================================\n",
      "均价区间\n",
      "1-3万    1939\n",
      "1万以内     753\n",
      "3-5万     262\n",
      "5万以上      16\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:面积户型================================\n",
      "面积户型\n",
      "小户型     1327\n",
      "中户型     1311\n",
      "大户型      233\n",
      "超大户型      99\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:成交周期区间================================\n",
      "成交周期区间\n",
      "半年以上成交    1433\n",
      "近六个月成交     549\n",
      "近三个月成交     546\n",
      "近一个月成交     442\n",
      "Name: count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "columns_to_analyze = ['室','厅', '朝向分类', '装修', '楼层等级', '成交价格区间', '均价区间','面积户型','成交周期区间']\n",
    "for city in cities:\n",
    "    df = datazs[city]\n",
    "    for i in columns_to_analyze:\n",
    "        print(f'==========================={city}:{i}================================')\n",
    "        print(df[i].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "64b7e4fa",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "===========================东莞:差价（万）================================\n",
      "Series([], Name: count, dtype: int64)\n",
      "===========================东莞:差价分类（金额）================================\n",
      "差价分类（金额）\n",
      "未分类（暂无数据）    2880\n",
      "Name: count, dtype: int64\n",
      "===========================东莞:价格数据状态================================\n",
      "价格数据状态\n",
      "暂无数据    2880\n",
      "Name: count, dtype: int64\n",
      "===========================中山:差价（万）================================\n",
      "差价（万）\n",
      "-5.0     195\n",
      "-7.0     178\n",
      "-8.0     139\n",
      "-10.0    138\n",
      "-4.0     136\n",
      "        ... \n",
      "-37.2      1\n",
      "-16.5      1\n",
      " 0.3       1\n",
      " 2.0       1\n",
      "-11.3      1\n",
      "Name: count, Length: 318, dtype: int64\n",
      "===========================中山:差价分类（金额）================================\n",
      "差价分类（金额）\n",
      "小幅降价    1793\n",
      "基本持平     956\n",
      "明显降价      85\n",
      "大幅降价      11\n",
      "小幅溢价       6\n",
      "Name: count, dtype: int64\n",
      "===========================中山:价格数据状态================================\n",
      "价格数据状态\n",
      "有效数据    2851\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:差价（万）================================\n",
      "差价（万）\n",
      "-15.0    5\n",
      "-10.0    4\n",
      "-24.0    3\n",
      "-4.0     2\n",
      "-30.0    2\n",
      "-40.0    2\n",
      "-39.0    2\n",
      "-18.0    2\n",
      "-20.0    2\n",
      "-8.0     2\n",
      "-12.5    1\n",
      "-32.1    1\n",
      "-26.0    1\n",
      "-66.4    1\n",
      "-24.5    1\n",
      "-14.0    1\n",
      "-17.2    1\n",
      "-9.0     1\n",
      "-3.0     1\n",
      "-27.5    1\n",
      "-53.0    1\n",
      "-34.0    1\n",
      "-12.0    1\n",
      "-31.0    1\n",
      "-11.6    1\n",
      "-19.8    1\n",
      "-41.4    1\n",
      "-80.0    1\n",
      "-35.0    1\n",
      "-90.0    1\n",
      "-19.4    1\n",
      "-32.0    1\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:差价分类（金额）================================\n",
      "差价分类（金额）\n",
      "未分类（暂无数据）    2859\n",
      "小幅降价           31\n",
      "明显降价           14\n",
      "基本持平            3\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:价格数据状态================================\n",
      "价格数据状态\n",
      "暂无数据    2859\n",
      "有效数据      48\n",
      "Name: count, dtype: int64\n",
      "===========================广州:差价（万）================================\n",
      "差价（万）\n",
      "-10.0     118\n",
      "-5.0       86\n",
      "-15.0      85\n",
      "-20.0      81\n",
      "-30.0      73\n",
      "         ... \n",
      "-370.0      1\n",
      "-4.1        1\n",
      "-88.0       1\n",
      "-30.7       1\n",
      "-22.7       1\n",
      "Name: count, Length: 474, dtype: int64\n",
      "===========================广州:差价分类（金额）================================\n",
      "差价分类（金额）\n",
      "小幅降价         1846\n",
      "明显降价          645\n",
      "基本持平          302\n",
      "大幅降价          100\n",
      "未分类（暂无数据）      21\n",
      "明显溢价            3\n",
      "小幅溢价            2\n",
      "大幅溢价            1\n",
      "Name: count, dtype: int64\n",
      "===========================广州:价格数据状态================================\n",
      "价格数据状态\n",
      "有效数据    2899\n",
      "暂无数据      21\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:差价（万）================================\n",
      "差价（万）\n",
      "-5.0      249\n",
      "-4.0      233\n",
      "-3.0      228\n",
      "-2.0      196\n",
      "-6.0      165\n",
      "         ... \n",
      "-62.0       1\n",
      "-9.2        1\n",
      "-145.0      1\n",
      "-7.3        1\n",
      " 0.7        1\n",
      "Name: count, Length: 300, dtype: int64\n",
      "===========================惠州:差价分类（金额）================================\n",
      "差价分类（金额）\n",
      "基本持平    1590\n",
      "小幅降价    1326\n",
      "明显降价      69\n",
      "大幅降价      13\n",
      "小幅溢价       1\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:价格数据状态================================\n",
      "价格数据状态\n",
      "有效数据    2999\n",
      "Name: count, dtype: int64\n",
      "===========================江门:差价（万）================================\n",
      "差价（万）\n",
      " 0.0     544\n",
      "-2.0     233\n",
      "-1.0     221\n",
      "-3.0     169\n",
      "-4.0     114\n",
      "        ... \n",
      "-15.2      1\n",
      " 14.0      1\n",
      " 0.9       1\n",
      "-13.2      1\n",
      "-17.5      1\n",
      "Name: count, Length: 265, dtype: int64\n",
      "===========================江门:差价分类（金额）================================\n",
      "差价分类（金额）\n",
      "基本持平    2505\n",
      "小幅降价     452\n",
      "明显降价      21\n",
      "小幅溢价      12\n",
      "大幅降价       3\n",
      "Name: count, dtype: int64\n",
      "===========================江门:价格数据状态================================\n",
      "价格数据状态\n",
      "有效数据    2993\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:差价（万）================================\n",
      "差价（万）\n",
      "-10.0     114\n",
      "-20.0     113\n",
      "-30.0     101\n",
      "-15.0      96\n",
      "-25.0      88\n",
      "         ... \n",
      "-262.0      1\n",
      "-8.8        1\n",
      "-13.3       1\n",
      "-2.2        1\n",
      "-36.5       1\n",
      "Name: count, Length: 484, dtype: int64\n",
      "===========================深圳:差价分类（金额）================================\n",
      "差价分类（金额）\n",
      "小幅降价    1638\n",
      "明显降价     942\n",
      "基本持平     204\n",
      "大幅降价     191\n",
      "小幅溢价      16\n",
      "明显溢价       6\n",
      "大幅溢价       3\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:价格数据状态================================\n",
      "价格数据状态\n",
      "有效数据    3000\n",
      "Name: count, dtype: int64\n",
      "===========================清远:差价（万）================================\n",
      "Series([], Name: count, dtype: int64)\n",
      "===========================清远:差价分类（金额）================================\n",
      "差价分类（金额）\n",
      "未分类（暂无数据）    2989\n",
      "Name: count, dtype: int64\n",
      "===========================清远:价格数据状态================================\n",
      "价格数据状态\n",
      "暂无数据    2989\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:差价（万）================================\n",
      "差价（万）\n",
      "-5.0     221\n",
      "-4.0     191\n",
      "-3.0     188\n",
      "-2.0     168\n",
      " 0.0     137\n",
      "        ... \n",
      " 2.8       1\n",
      " 35.0      1\n",
      "-15.6      1\n",
      "-58.0      1\n",
      "-13.2      1\n",
      "Name: count, Length: 284, dtype: int64\n",
      "===========================湛江:差价分类（金额）================================\n",
      "差价分类（金额）\n",
      "基本持平    1609\n",
      "小幅降价    1309\n",
      "明显降价      48\n",
      "小幅溢价      12\n",
      "明显溢价       2\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:价格数据状态================================\n",
      "价格数据状态\n",
      "有效数据    2980\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:差价（万）================================\n",
      "差价（万）\n",
      "-5.0      159\n",
      "-10.0     157\n",
      "-8.0      119\n",
      "-15.0     117\n",
      "-7.0      113\n",
      "         ... \n",
      "-34.2       1\n",
      "-4.2        1\n",
      "-104.0      1\n",
      "-22.4       1\n",
      "-32.2       1\n",
      "Name: count, Length: 376, dtype: int64\n",
      "===========================珠海:差价分类（金额）================================\n",
      "差价分类（金额）\n",
      "小幅降价    1918\n",
      "基本持平     576\n",
      "明显降价     424\n",
      "大幅降价      50\n",
      "小幅溢价       2\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:价格数据状态================================\n",
      "价格数据状态\n",
      "有效数据    2970\n",
      "Name: count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "columns_to_analyze = ['差价（万）','差价分类（金额）','价格数据状态']\n",
    "for city in cities:\n",
    "    df = datazs[city]\n",
    "    for i in columns_to_analyze:\n",
    "        print(f'==========================={city}:{i}================================')\n",
    "        print(df[i].value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "940d6b8d",
   "metadata": {},
   "source": [
    "# 合并数据并保存"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "4acc5310",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "已合并所有城市数据，总行数: 29489\n",
      "包含城市: ['东莞', '中山', '佛山', '广州', '惠州', '江门', '深圳', '清远', '湛江', '珠海']\n",
      "\n",
      " 已将合并后的数据保存至: 成交集合/广东成交二手房数据.csv\n"
     ]
    }
   ],
   "source": [
    "all_data = pd.concat(\n",
    "    [datazs[city] for city in cities],\n",
    "    ignore_index=True  # 重置索引\n",
    ")\n",
    "\n",
    "# 查看合并后的数据概况\n",
    "print(f\"已合并所有城市数据，总行数: {len(all_data)}\")\n",
    "print(f\"包含城市: {all_data['城市'].unique().tolist()}\")\n",
    "\n",
    "# 按照指定顺序重新排列列\n",
    "visualization_cols = [\n",
    "    '新编号','小区', '城市',  # 地理位置信息\n",
    "    '房型', '室', '厅','装修',  # 房型结构\n",
    "    '面积', '面积户型',  # 面积分类\n",
    "    '朝向', '朝向分类',  # 朝向分类\n",
    "    '楼层', '楼层等级',  # 楼层信息\n",
    "    '成交时间','挂牌价格',\n",
    "    '成交周期', '成交周期区间',  # 成交周期分类\n",
    "    '成交价格', '成交价格区间',  # 成交价格分类\n",
    "    '均价', '均价区间',  # 均价分类\n",
    "    '差价（万）', '差价分类（金额）',  # 差价分类\n",
    "    '价格数据状态'  # 数据有效性标记\n",
    "]\n",
    "\n",
    "all_data = all_data.reindex(columns=visualization_cols)\n",
    "\n",
    "# 保存为CSV文件\n",
    "if not os.path.exists('成交集合'):\n",
    "    os.makedirs('成交集合')\n",
    "output_path = f\"{'成交集合'}/广东成交二手房数据.csv\"\n",
    "all_data.to_csv(\n",
    "    output_path,\n",
    "    index=False,           # 不保存行索引\n",
    "    na_rep='nan',          # 缺失值用nan表示\n",
    "    encoding='utf-8-sig'   \n",
    ")\n",
    "\n",
    "print(f\"\\n 已将合并后的数据保存至: {output_path}\") "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f754dff8",
   "metadata": {},
   "source": [
    "# 分类数据保存"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "f73bbcf3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "===========================东莞================================\n",
      "东莞导入成功\n",
      "===========================中山================================\n",
      "中山导入成功\n",
      "===========================佛山================================\n",
      "佛山导入成功\n",
      "===========================广州================================\n",
      "广州导入成功\n",
      "===========================惠州================================\n",
      "惠州导入成功\n",
      "===========================江门================================\n",
      "江门导入成功\n",
      "===========================深圳================================\n",
      "深圳导入成功\n",
      "===========================清远================================\n",
      "清远导入成功\n",
      "===========================湛江================================\n",
      "湛江导入成功\n",
      "===========================珠海================================\n",
      "珠海导入成功\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import json\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "columns_to_analyze = [\n",
    "    '房型', '室', '厅', '面积户型',\n",
    "    '朝向分类', '装修', '楼层等级',\n",
    "    '成交价格区间', '均价区间',\n",
    "    '成交时间', '成交周期区间',\n",
    "    '差价（万）', '差价分类（金额）', '价格数据状态'\n",
    "]\n",
    "\n",
    "dir_name = '成交分类'\n",
    "if not os.path.exists(dir_name):\n",
    "    os.makedirs(dir_name)\n",
    "\n",
    "def replace_nan_with_null(data):\n",
    "    \"\"\"\n",
    "    递归替换数据中的 NaN 为 null\n",
    "    \"\"\"\n",
    "    if isinstance(data, dict):\n",
    "        return {key: replace_nan_with_null(value) for key, value in data.items()}\n",
    "    elif isinstance(data, list):\n",
    "        return [replace_nan_with_null(item) for item in data]\n",
    "    elif isinstance(data, float) and np.isnan(data):\n",
    "        return None\n",
    "    return data\n",
    "\n",
    "for city, df in datazs.items():\n",
    "    print(f'==========================={city}================================')\n",
    "    city_stats = {}\n",
    "    city_stats['城市'] = city\n",
    "\n",
    "    # 过滤掉总价和单价缺失的行\n",
    "    df_clean = df[(df['成交价格'].notnull()) & (df['均价'].notnull()) & df['成交周期']].copy()\n",
    "\n",
    "    # 计算平均值（保留2位小数）\n",
    "    avg_total_price = round(df_clean['成交价格'].mean(), 2)\n",
    "    avg_unit_price = round(df_clean['均价'].mean(), 2)\n",
    "\n",
    "    # 存入统计字典\n",
    "    city_stats['平均成交价格（万）'] = avg_total_price\n",
    "    city_stats['平均均价（元/㎡）'] = avg_unit_price\n",
    "\n",
    "    # 获取TOP10小区成交数量\n",
    "    community_stats = df.groupby('小区').agg(\n",
    "        成交数量=('新编号', 'count'),\n",
    "        平均成交价格=('成交价格', 'mean'),\n",
    "        平均均价=('均价', 'mean'),\n",
    "        平均成交周期=('成交周期', 'mean')\n",
    "    ).reset_index()\n",
    "\n",
    "    # 保留两位小数\n",
    "    community_stats['平均成交价格'] = community_stats['平均成交价格'].round(2)\n",
    "    community_stats['平均均价'] = community_stats['平均均价'].round(2)\n",
    "    community_stats['平均成交周期'] = community_stats['平均成交周期'].round(2)\n",
    "\n",
    "    # 排序并取前10\n",
    "    top10_communities = community_stats.sort_values('成交数量', ascending=False).head(10)\n",
    "\n",
    "    # 转换为字典列表\n",
    "    top10_records = top10_communities.rename(columns={\n",
    "        '平均成交价格': '平均成交价格（万）',\n",
    "        '平均均价': '平均均价（元/㎡）',\n",
    "        '平均成交周期': '平均成交周期（天）'\n",
    "    }).to_dict('records')\n",
    "\n",
    "    city_stats['top10小区'] = top10_records\n",
    "\n",
    "    # 其他列分类统计\n",
    "    for col in columns_to_analyze:\n",
    "        counts = df[col].value_counts().to_dict()\n",
    "        city_stats[col] = counts\n",
    "\n",
    "    # 替换 NaN 为 null\n",
    "    city_stats = replace_nan_with_null(city_stats)\n",
    "\n",
    "    print(f'{city}导入成功')\n",
    "    with open(f\"{dir_name}/{city}_成交分类统计结果.json\", \"w\", encoding=\"utf-8\") as f:\n",
    "        json.dump(city_stats, f, ensure_ascii=False, indent=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "55c27c39",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "已过滤掉 8749 条「价格数据状态=1」的记录\n",
      "已合并所有城市数据，筛选后总行数: 20740\n",
      "包含城市: ['中山', '佛山', '广州', '惠州', '江门', '深圳', '湛江', '珠海']\n",
      "\n",
      " 已将合并后的数据保存至: 成交集合/es广东成交二手房数据.csv\n"
     ]
    }
   ],
   "source": [
    "# 合并前过滤掉「价格数据状态=1」的记录\n",
    "filtered_data = [\n",
    "    datazs[city][datazs[city]['价格数据状态'] != '暂无数据']  # 筛选价格数据状态不等于1的行\n",
    "    for city in cities\n",
    "]\n",
    "\n",
    "all_data = pd.concat(\n",
    "    filtered_data,\n",
    "    ignore_index=True  # 重置索引\n",
    ")\n",
    "\n",
    "# 查看合并后的数据概况（新增筛选后的数据量提示）\n",
    "original_total = sum([len(datazs[city]) for city in cities])  # 原始总行数\n",
    "filtered_total = len(all_data)  # 筛选后的总行数\n",
    "print(f\"已过滤掉 {original_total - filtered_total} 条「价格数据状态=1」的记录\")\n",
    "print(f\"已合并所有城市数据，筛选后总行数: {filtered_total}\")\n",
    "print(f\"包含城市: {all_data['城市'].unique().tolist()}\")\n",
    "\n",
    "# 以下代码（列重排、保存）保持不变\n",
    "visualization_cols = [\n",
    "    '新编号','小区', '城市',  # 地理位置信息\n",
    "    '房型', '室', '厅','装修',  # 房型结构\n",
    "    '面积', '面积户型',  # 面积分类\n",
    "    '朝向', '朝向分类',  # 朝向分类\n",
    "    '楼层', '楼层等级',  # 楼层信息\n",
    "    '成交时间','挂牌价格',\n",
    "    '成交周期', '成交周期区间',  # 成交周期分类\n",
    "    '成交价格', '成交价格区间',  # 成交价格分类\n",
    "    '均价', '均价区间',  # 均价分类\n",
    "    '差价（万）', '差价分类（金额）',  # 差价分类\n",
    "    '价格数据状态'  # 数据有效性标记\n",
    "]\n",
    "\n",
    "all_data = all_data.reindex(columns=visualization_cols)\n",
    "\n",
    "if not os.path.exists('成交集合'):\n",
    "    os.makedirs('成交集合')\n",
    "output_path = f\"{'成交集合'}/es广东成交二手房数据.csv\"\n",
    "all_data.to_csv(\n",
    "    output_path,\n",
    "    index=False,           \n",
    "    na_rep='nan',          \n",
    "    encoding='utf-8-sig'   \n",
    ")\n",
    "\n",
    "print(f\"\\n 已将合并后的数据保存至: {output_path}\") "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
